<?php

class Nebula {

    public static function nebulas_for_uid($uid) {
        $uid = DB::esc($uid);
        $result = DB::query("SELECT nebula.nid AS nid,name,icon,user_nebula.uid AS uid,prof.prof_vector AS profv FROM nebula
                    JOIN user_nebula
                    ON (user_nebula.nid=nebula.nid)
                    JOIN (SELECT prof_vector, uid from user)prof
                    ON (prof.uid = user_nebula.uid)
                    WHERE user_nebula.uid = '$uid'");
        $nebulas = array();
        while ($nebula = $result->fetch_object()) {
            $nebulas[] = $nebula;
        }
        return $nebulas;
    }

    public static function get_common_nebulas($uid_to) {
        $uid_from = $_SESSION['user']['uid'];
        $result = DB::query("SELECT nebula.nid AS nid,name,icon FROM nebula
                    JOIN user_nebula
                    ON (user_nebula.nid=nebula.nid)
                    JOIN (SELECT user_nebula.nid FROM user_nebula
						  WHERE user_nebula.uid = '$uid_from')otherTags
                    ON (otherTags.nid=user_nebula.nid)
                    WHERE user_nebula.uid = '$uid_to'");
        $nebulas = array();
        while ($nebula = $result->fetch_object()) {
            $nebulas[] = $nebula;
        }
        return $nebulas;
    }

    public static function get_nebula_name($nid) {
        $uid = $_SESSION['user']['uid'];
        $query = "SELECT name, nid, tagged.tagged AS tagged FROM nebula
            LEFT JOIN (SELECT user_nebula.tagged, user_nebula.uid FROM user_nebula WHERE user_nebula.nid = '$nid')tagged
            ON (tagged.uid = '$uid')
            WHERE nid = '$nid'";
        $result = DB::query($query);
        return $result->fetch_object();
    }

    // this function should be called carefully
    public static function all_nebulas() {
        $uid = $_SESSION['user']['uid'];
        $result = DB::query("SELECT nid,name,nebula.uid AS uid,icon,description,d0,d1,d2,ts, prof.prof_vector AS profv FROM nebula
                 JOIN (SELECT user.prof_vector, user.uid from user)prof
                 ON(prof.uid = '$uid')");
        $nebulas = array();
        while ($nebula = $result->fetch_object()) {
            $nebulas[] = $nebula;
        }
        return $nebulas;
    }

    // this function should be used for the most of the time
    public static function nebulas($options) {
        $query = "SELECT * FROM nebula";
        if (isset($options['upper'])) {
            $upper = DB::esc($options['upper']);
            $lower = 0;
            if (isset($options['lower'])) {
                $lower = DB::esc($options['lower']);
            }
            $query .= " LIMIT $lower, $upper";
        }

        $result = DB::query($query);
        $nebulas = array();
        while ($nebula = $result->fetch_object()) {
            $nebulas[] = $nebula;
        }
        return $nebulas;
    }

    public static function add_nebula($info) {
        $name = DB::esc($info['name']);
        $icon = DB::esc($info['icon']);
        $uid = $_SESSION['user']['uid'];
        $description = DB::esc($info['description']);
        if (DB::hasData("SELECT * FROM nebula WHERE name = '$name'"))
            return 0;
        else
            $query = "INSERT INTO nebula (name, icon, uid, description) VALUES ('$name','$icon', '$uid', '$description')";
        DB::query($query);
        return DB::getMySQLiObject()->insert_id;
    }

    public static function tag_nebula($in) {
        $uid = $_SESSION['user']['uid'];
        $tag = (int) $in;
        if (DB::query("INSERT INTO user_nebula (uid, nid) VALUES ('$uid', '$tag')")) {
            return 1;
        } else {
            return 0;
        }
    }

    public static function untag_nebula($nid) {
        $uid = $_SESSION['user']['uid'];
        $nid = (int) $nid;
        DB::query("DELETE FROM user_nebula WHERE nid = '$nid' AND uid = '$uid'");
        return 1;
    }

    // get the posts and galaxies of a nebula, combine them in a sorted array
    public static function get_nebula_content($nid) {
        $nid = DB::esc($nid);
        $uid = $_SESSION['user']['uid'];
        $post_result = DB::query("
				SELECT user.uid, username, avatar,
				post.ts AS ts, UNIX_TIMESTAMP(post.ts) AS seconds, post.title AS title, nid, name, post.pid, toshow, mps.pid IS NOT NULL AS marked, tagged.tagged IS NOT NULL AS tagged
				FROM post_nebula
				JOIN post
				USING ( pid )
				JOIN user ON ( post.uid = user.uid ) JOIN nebula USING( nid )
				LEFT JOIN (SELECT mark_post.pid, mark_post.uid FROM mark_post WHERE mark_post.uid = '$uid')mps ON (post.pid = mps.pid)
                LEFT JOIN (SELECT user_nebula.tagged, user_nebula.uid FROM user_nebula WHERE user_nebula.nid = '$nid')tagged ON (tagged.uid = '$uid')
				WHERE post_nebula.nid = '$nid' AND toshow = 'true'
				ORDER BY ts DESC
				");
        $galaxy_result = DB::query("
				SELECT galaxy_rated.gid IS NOT NULL AS rated, galaxy_lit, galaxy_dim, user.uid, username, avatar, galaxy.ts AS ts, users.joined as joined, UNIX_TIMESTAMP( galaxy.ts ) AS seconds, galaxy.title AS title, gid, nid, name, tagged.tagged IS NOT NULL AS tagged
				FROM galaxy_nebula
				JOIN (
					SELECT * 
					FROM galaxy
					WHERE gid NOT 
					IN (
						SELECT gid
						FROM galaxy
						JOIN galaxy_post
						USING ( gid )
				    )
				) galaxy
				USING ( gid ) 
                LEFT JOIN (
							SELECT uid IS NOT NULL AS joined, gid
							FROM chatuser
							WHERE chatuser.uid = '$uid' AND chatuser.toshow = 'true'
						)users
				USING ( gid )
                LEFT JOIN (
                    SELECT user_nebula.tagged, user_nebula.uid FROM user_nebula WHERE user_nebula.nid = '$nid'
                    )tagged
                ON (tagged.uid = '$uid')
				JOIN user ON ( galaxy.uid = user.uid ) 
				JOIN nebula
				USING ( nid )
				LEFT JOIN  (
							SELECT gid, SUM(score) AS galaxy_lit FROM galaxy_score WHERE score=1 GROUP BY gid
							)galaxy_lit_sum
				USING (gid)
				LEFT JOIN  (
							SELECT gid, SUM(score) AS galaxy_dim FROM galaxy_score WHERE score=-1 GROUP BY gid
							)galaxy_dim_sum
				USING (gid)
				LEFT JOIN (
							SELECT gid, uid FROM galaxy_score WHERE uid='$uid'
							)galaxy_rated
				USING (gid)
				WHERE galaxy_nebula.nid = '$nid'
				ORDER BY ts DESC
				");
        $res = array();
        $post = $post_result->fetch_object();
        $galaxy = $galaxy_result->fetch_object();
        $mg_flag = get_magic_quotes_gpc();
        while ($post || $galaxy) {
            if ($post && $galaxy) {
                if ($post->seconds > $galaxy->seconds) {
                    if ($mg_flag) {
                        $post->title = stripslashes($post->title);
                    }
                    $res[] = Array('type' => 'post', 'data' => $post);
                    $post = $post_result->fetch_object();
                } else {
                    if ($mg_flag) {
                        $galaxy->title = stripslashes($galaxy->title);
                    }
                    $res[] = Array('type' => 'galaxy', 'data' => $galaxy);
                    $galaxy = $galaxy_result->fetch_object();
                }
            } else if ($post) {
                if ($mg_flag) {
                    $post->title = stripslashes($post->title);
                }
                $res[] = Array('type' => 'post', 'data' => $post);
                $post = $post_result->fetch_object();
            } else {
                if ($mg_flag) {
                    $galaxy->title = stripslashes($galaxy->title);
                }
                $res[] = Array('type' => 'galaxy', 'data' => $galaxy);
                $galaxy = $galaxy_result->fetch_object();
            }
        }
        return $res;
    }

    public static function get_top_nebulas($num) {
        if ($num == null) {
            $num = 4;
        }
        $result = DB::query("
				SELECT nid, name, icon, description, count( nid ) AS heat FROM nebula
				LEFT JOIN post_nebula USING( nid )
				LEFT JOIN galaxy_nebula USING( nid )
				GROUP BY nid
				ORDER BY heat DESC
				LIMIT 0, $num
				");
        $nebulas = array();
        while ($nebula = $result->fetch_object()) {
            $nebulas[] = $nebula;
        }
        return $nebulas;
    }

}

?>
